import { sqliteTable, text, integer, real, index } from 'drizzle-orm/sqlite-core';
import { sql } from 'drizzle-orm';

// 用户表
export const users = sqliteTable('users', {
  id: text('id').primaryKey(),
  username: text('username').notNull().unique(),
  email: text('email').notNull().unique(),
  password: text('password').notNull(),
  role: text('role').notNull().default('user'), // admin, user, guest
  avatar: text('avatar'),
  status: text('status').notNull().default('active'), // active, inactive, banned
  lastLoginAt: text('last_login_at'),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at').notNull().default(sql`CURRENT_TIMESTAMP`)
}, (table) => ({
  usernameIdx: index('users_username_idx').on(table.username),
  emailIdx: index('users_email_idx').on(table.email),
  statusIdx: index('users_status_idx').on(table.status)
}));

// 产品分类表
export const productCategories = sqliteTable('product_categories', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  description: text('description'),
  image: text('image'),
  sort: integer('sort').notNull().default(0),
  status: text('status').notNull().default('active'), // active, inactive
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at').notNull().default(sql`CURRENT_TIMESTAMP`)
}, (table) => ({
  statusIdx: index('product_categories_status_idx').on(table.status),
  sortIdx: index('product_categories_sort_idx').on(table.sort)
}));

// 产品表
export const products = sqliteTable('products', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  type: text('type').notNull(),
  categoryId: text('category_id'),
  description: text('description'),
  content: text('content'),
  priceRange: text('price_range'),
  images: text('images'), // JSON array
  features: text('features'), // JSON array - 产品特点
  craftTypes: text('craft_types'), // JSON array - 工艺特点
  specifications: text('specifications'), // JSON object - 技术规格
  status: text('status').notNull().default('published'), // published, disabled
  sort: integer('sort').notNull().default(0), // 排序，0为默认，数字越小越靠前
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at').notNull().default(sql`CURRENT_TIMESTAMP`)
}, (table) => ({
  categoryIdx: index('products_category_idx').on(table.categoryId),
  statusIdx: index('products_status_idx').on(table.status),
  sortIdx: index('products_sort_idx').on(table.sort)
}));

// 删除复杂的产品相关表，简化为单一产品表结构
// craftTypes, productSpecifications, productImages, priceConfigurations, processParameters 表已删除
// 相关数据现在存储在 products 表的 JSON 字段中

// 案例分类表
export const caseCategories = sqliteTable('case_categories', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  description: text('description'),
  image: text('image'),
  sort: integer('sort').notNull().default(0),
  status: text('status').notNull().default('active'), // active, inactive
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at').notNull().default(sql`CURRENT_TIMESTAMP`)
}, (table) => ({
  statusIdx: index('case_categories_status_idx').on(table.status),
  sortIdx: index('case_categories_sort_idx').on(table.sort)
}));

// 案例表
export const cases = sqliteTable('cases', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
  categoryId: text('category_id'),
  industry: text('industry'),
  description: text('description'),
  content: text('content'),
  services: text('services'), // JSON array - 服务内容（相当于标签）
  images: text('images'), // JSON array
  highlights: text('highlights'),
  technologies: text('technologies'), // JSON array
  clientName: text('client_name'),
  startDate: text('start_date'), // 项目开始时间  
  endDate: text('end_date'), // 项目结束时间
  duration: text('duration'), // 项目周期（自动计算）
  completedAt: text('completed_at'),
  location: text('location'),
  projectValue: text('project_value'),
  challenges: text('challenges'),
  solutions: text('solutions'),
  status: text('status').notNull().default('published'), // published, disabled
  sort: integer('sort').notNull().default(0), // 排序，0为默认，数字越小越靠前
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at').notNull().default(sql`CURRENT_TIMESTAMP`)
}, (table) => ({
  categoryIdx: index('cases_category_idx').on(table.categoryId),
  statusIdx: index('cases_status_idx').on(table.status),
  sortIdx: index('cases_sort_idx').on(table.sort)
}));

// 标签表
export const tags = sqliteTable('tags', {
  id: text('id').primaryKey(),
  name: text('name').notNull().unique(),
  color: text('color'),
  description: text('description'),
  usageCount: integer('usage_count').notNull().default(0),
  status: text('status').notNull().default('active'), // active, inactive
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at').notNull().default(sql`CURRENT_TIMESTAMP`)
});

// 设置表
export const settings = sqliteTable('settings', {
  id: text('id').primaryKey(),
  key: text('key').notNull().unique(),
  value: text('value'),
  type: text('type').notNull().default('string'), // string, number, boolean, json
  description: text('description'),
  category: text('category').notNull().default('general'),
  isPublic: integer('is_public').notNull().default(0),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at').notNull().default(sql`CURRENT_TIMESTAMP`)
}, (table) => ({
  keyIdx: index('settings_key_idx').on(table.key),
  categoryIdx: index('settings_category_idx').on(table.category)
}));

// 文件上传表
export const uploads = sqliteTable('uploads', {
  id: text('id').primaryKey(),
  filename: text('filename').notNull(),
  originalName: text('original_name').notNull(),
  mimeType: text('mime_type').notNull(),
  size: integer('size').notNull(),
  path: text('path').notNull(),
  url: text('url').notNull(),
  uploadedBy: text('uploaded_by'),
  status: text('status').notNull().default('active'), // active, deleted
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at').notNull().default(sql`CURRENT_TIMESTAMP`)
}, (table) => ({
  uploadedByIdx: index('uploads_uploaded_by_idx').on(table.uploadedBy),
  statusIdx: index('uploads_status_idx').on(table.status)
}));

// 联系人表
export const contacts = sqliteTable('contacts', {
  id: text('id').primaryKey(),
  name: text('name').notNull(),
  position: text('position'),
  description: text('description'),
  phone: text('phone'),
  email: text('email'),
  wechat: text('wechat'),
  qq: text('qq'),
  expertise: text('expertise'), // JSON array
  workHours: text('work_hours'),
  sort: integer('sort').notNull().default(0),
  status: text('status').notNull().default('active'), // active, inactive
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at').notNull().default(sql`CURRENT_TIMESTAMP`)
}, (table) => ({
  statusIdx: index('contacts_status_idx').on(table.status),
  sortIdx: index('contacts_sort_idx').on(table.sort)
}));

// 横幅广告表
export const banners = sqliteTable('banners', {
  id: text('id').primaryKey(),
  title: text('title').notNull(),
  subtitle: text('subtitle'),
  image: text('image').notNull(),
  link: text('link'),
  target: text('target').notNull().default('_self'), // _self, _blank
  sort: integer('sort').notNull().default(0),
  status: text('status').notNull().default('active'), // active, inactive
  startDate: text('start_date'),
  endDate: text('end_date'),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`),
  updatedAt: text('updated_at').notNull().default(sql`CURRENT_TIMESTAMP`)
}, (table) => ({
  statusIdx: index('banners_status_idx').on(table.status),
  sortIdx: index('banners_sort_idx').on(table.sort)
}));

// 系统日志表
export const systemLogs = sqliteTable('system_logs', {
  id: text('id').primaryKey(),
  level: text('level').notNull(), // info, warn, error, debug
  module: text('module').notNull(), // 模块名称
  action: text('action').notNull(), // 操作名称
  message: text('message').notNull(),
  userId: text('user_id'),
  username: text('username'),
  ip: text('ip'),
  userAgent: text('user_agent'),
  requestId: text('request_id'),
  metadata: text('metadata'), // JSON object
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`)
}, (table) => ({
  levelIdx: index('system_logs_level_idx').on(table.level),
  moduleIdx: index('system_logs_module_idx').on(table.module),
  userIdIdx: index('system_logs_user_id_idx').on(table.userId),
  createdAtIdx: index('system_logs_created_at_idx').on(table.createdAt)
}));

// 数据备份表
export const backups = sqliteTable('backups', {
  id: text('id').primaryKey(),
  filename: text('filename').notNull(),
  filePath: text('file_path').notNull(),
  fileSize: integer('file_size').notNull(),
  backupType: text('backup_type').notNull(), // manual, auto, scheduled
  status: text('status').notNull().default('completed'), // pending, processing, completed, failed
  createdBy: text('created_by'),
  createdAt: text('created_at').notNull().default(sql`CURRENT_TIMESTAMP`)
}, (table) => ({
  statusIdx: index('backups_status_idx').on(table.status),
  createdByIdx: index('backups_created_by_idx').on(table.createdBy)
}));

// 数据库架构重构说明：
// 1. 统一字段命名规范，使用驼峰命名法
// 2. 添加必要的索引以提升查询性能
// 3. 规范化JSON字段的使用
// 4. 确保与前端数据结构的一致性
// 5. 简化复杂的关联关系，提高维护性